/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package ysw.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import ysw.dao.MessageDao;
import ysw.databaseconnection.DBConn;
import ysw.vo.Message;

/**
 *
 * @author Administrator
 */
public class MessageJdbc implements MessageDao{
    private  Connection conn=null;
    private PreparedStatement psmt=null;
    private ResultSet rs=null;
    public MessageJdbc(){}
    public Connection getConn(){
        try{
            if(this.conn==null||this.conn.isClosed()){
                DBConn mc=new DBConn();
                this.conn=mc.getConn();
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
        return conn;
    }

    public Message addMessage(Message message) {
      String sql="insert into messages(cid,cname,mtime,content) values(?,?,?,?)";
        try{
     
            psmt=this.getConn().prepareStatement(sql);
            
            psmt.setInt(1,message.getCid());   
             psmt.setString(2,message.getCname());  
             psmt.setDate(3,message.getMtime());
             psmt.setString(4,message.getContent());
         
       
            psmt.execute();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            try{
                if(psmt!=null){
                    psmt.close();
                    psmt=null;
                }
                if(conn!=null){
                    conn.close();
                    conn=null;
                }
            }catch(SQLException e){
                        e.printStackTrace();
                        }
            }
            return message;
    }

    
    public List showMessage() throws SQLException {
       String sql="select * from messages";
       
        List messageList=new ArrayList();
    try{
        psmt=this.getConn().prepareStatement(sql);
        rs=psmt.executeQuery();
        while(rs.next()){
            Message message=new Message();
            message.setId(rs.getInt("id"));
            message.setCid(rs.getInt("cid"));
            message.setCname(rs.getString("cname"));
            message.setMtime(rs.getDate("mtime"));
            message.setContent(rs.getString("content"));
            message.setAnswer(rs.getString("answer")); 
            
            messageList.add(message);
     
  
        }
        return messageList;
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        try{
             if(rs!=null){
                   rs.close();
                   rs=null;
                }
                if(psmt!=null){
                    psmt.close();
                    psmt=null;
                }
                if(conn!=null){
                    conn.close();
                    conn=null;
                }
            }catch(SQLException e){
                        e.printStackTrace();
                        }
            }
            return messageList;
    }
public Message showOneMessage(Integer id){
     ResultSet rs=null;
    String sql="select * from messages where id="+id;
   Message message=new Message();
    try{
      psmt=this.getConn().prepareStatement(sql);
        rs=psmt.executeQuery();
        while(rs.next()){
            message.setId(rs.getInt("id"));
            message.setCid(rs.getInt("cid"));
            message.setCname(rs.getString("cname"));
            message.setMtime(rs.getDate("mtime"));
            message.setContent(rs.getString("content"));
            message.setAnswer(rs.getString("answer"));
        }
 
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        try{
            psmt.close();
              
            }catch(SQLException e){
                        e.printStackTrace();
                        }
        try{conn.close();
        
        }catch(SQLException e){
             e.printStackTrace();
        }
            }
            return message;
}


 public Message updateSaveMessage(Message message){
          String sql="update messages set id=?,cid=?,cname=?,mtime=?,content=?,answer=? where id="+message.getId();
     try{
            
          psmt=this.getConn().prepareStatement(sql);
           psmt.setInt(1,message.getId());   
             psmt.setInt(2,message.getCid());   
             psmt.setString(3,message.getCname());  
             psmt.setDate(4,message.getMtime());
             psmt.setString(5,message.getContent());
             psmt.setString(6,message.getAnswer());
          
            psmt.execute();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
         try{
             psmt.close();
            
         }catch(SQLException e){
             e.printStackTrace();
         }
     }
     return message;
}
}
